﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.IO;

namespace StudentManagementSystem
{
    // 学校类
    public class School
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public List<Class> Classes { get; set; }
    }

    // 班级类
    public class Class
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public List<Student> Students { get; set; }
    }

    // 学生类
    public class Student
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public int Age { get; set; }
    }

    // 数据库操作类
    public class DatabaseHelper
    {
        private string connectionString;

        public DatabaseHelper(string dbFilePath)
        {
            connectionString = $"Data Source={dbFilePath};Version=3;";
            if (!File.Exists(dbFilePath))
            {
                CreateDatabase();
            }
        }

        private void CreateDatabase()
        {
            SQLiteConnection.CreateFile("students.db");

            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();

                // 创建学校表
                string createSchoolTableQuery = "CREATE TABLE School (Id INTEGER PRIMARY KEY AUTOINCREMENT, Name TEXT)";
                using (var command = new SQLiteCommand(createSchoolTableQuery, connection))
                {
                    command.ExecuteNonQuery();
                }

                // 创建班级表
                string createClassTableQuery = "CREATE TABLE Class (Id INTEGER PRIMARY KEY AUTOINCREMENT, Name TEXT, SchoolId INTEGER, FOREIGN KEY(SchoolId) REFERENCES School(Id))";
                using (var command = new SQLiteCommand(createClassTableQuery, connection))
                {
                    command.ExecuteNonQuery();
                }

                // 创建学生表
                string createStudentTableQuery = "CREATE TABLE Student (Id INTEGER PRIMARY KEY AUTOINCREMENT, Name TEXT, Age INTEGER, ClassId INTEGER, FOREIGN KEY(ClassId) REFERENCES Class(Id))";
                using (var command = new SQLiteCommand(createStudentTableQuery, connection))
                {
                    command.ExecuteNonQuery();
                }

                // 创建日志表
                string createLogTableQuery = "CREATE TABLE Log (Id INTEGER PRIMARY KEY AUTOINCREMENT, Operation TEXT, Timestamp TEXT)";
                using (var command = new SQLiteCommand(createLogTableQuery, connection))
                {
                    command.ExecuteNonQuery();
                }
            }
        }

        // 创建学校
        public void CreateSchool(School school)
        {
            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();

                string insertQuery = "INSERT INTO School (Name) VALUES (@Name)";
                using (var command = new SQLiteCommand(insertQuery, connection))
                {
                    command.Parameters.AddWithValue("@Name", school.Name);
                    command.ExecuteNonQuery();

                    string logQuery = "INSERT INTO Log (Operation, Timestamp) VALUES ('创建学校', datetime('now'))";
                    using (var logCommand = new SQLiteCommand(logQuery, connection))
                    {
                        logCommand.ExecuteNonQuery();
                    }
                }
            }
        }

        // 创建班级
        public void CreateClass(Class @class, int schoolId)
        {
            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();

                string insertQuery = "INSERT INTO Class (Name, SchoolId) VALUES (@Name, @SchoolId)";
                using (var command = new SQLiteCommand(insertQuery, connection))
                {
                    command.Parameters.AddWithValue("@Name", @class.Name);
                    command.Parameters.AddWithValue("@SchoolId", schoolId);
                    command.ExecuteNonQuery();

                    string logQuery = "INSERT INTO Log (Operation, Timestamp) VALUES ('创建班级', datetime('now'))";
                    using (var logCommand = new SQLiteCommand(logQuery, connection))
                    {
                        logCommand.ExecuteNonQuery();
                    }
                }
            }
        }

        // 创建学生
        public void CreateStudent(Student student, int classId)
        {
            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();

                string insertQuery = "INSERT INTO Student (Name, Age, ClassId) VALUES (@Name, @Age, @ClassId)";
                using (var command = new SQLiteCommand(insertQuery, connection))
                {
                    command.Parameters.AddWithValue("@Name", student.Name);
                    command.Parameters.AddWithValue("@Age", student.Age);
                    command.Parameters.AddWithValue("@ClassId", classId);
                    command.ExecuteNonQuery();

                    string logQuery = "INSERT INTO Log (Operation, Timestamp) VALUES ('创建学生', datetime('now'))";
                    using (var logCommand = new SQLiteCommand(logQuery, connection))
                    {
                        logCommand.ExecuteNonQuery();
                    }
                }
            }
        }

        // 获取所有学校
        public List<School> GetAllSchools()
        {
            List<School> schools = new List<School>();

            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();

                string query = "SELECT * FROM School";
                using (var command = new SQLiteCommand(query, connection))
                {
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            School school = new School
                            {
                                Id = Convert.ToInt32(reader["Id"]),
                                Name = reader["Name"].ToString(),
                                Classes = new List<Class>()
                            };

                            schools.Add(school);
                        }
                    }
                }

                foreach (var school in schools)
                {
                    school.Classes = GetClassesBySchoolId(school.Id);
                }
            }

            return schools;
        }

        // 根据学校ID获取班级
        private List<Class> GetClassesBySchoolId(int schoolId)
        {
            List<Class> classes = new List<Class>();

            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();

                string query = "SELECT * FROM Class WHERE SchoolId = @SchoolId";
                using (var command = new SQLiteCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@SchoolId", schoolId);
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Class @class = new Class
                            {
                                Id = Convert.ToInt32(reader["Id"]),
                                Name = reader["Name"].ToString(),
                                Students = new List<Student>()
                            };

                            classes.Add(@class);
                        }
                    }
                }

                foreach (var @class in classes)
                {
                    @class.Students = GetStudentsByClassId(@class.Id);
                }
            }

            return classes;
        }

        // 根据班级ID获取学生
        private List<Student> GetStudentsByClassId(int classId)
        {
            List<Student> students = new List<Student>();

            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();

                string query = "SELECT * FROM Student WHERE ClassId = @ClassId";
                using (var command = new SQLiteCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@ClassId", classId);
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Student student = new Student
                            {
                                Id = Convert.ToInt32(reader["Id"]),
                                Name = reader["Name"].ToString(),
                                Age = Convert.ToInt32(reader["Age"])
                            };

                            students.Add(student);
                        }
                    }
                }
            }

            return students;
        }
    }

    class Program
    {
        static void Main(string[] args)
        {
            // 创建数据库辅助类实例
            DatabaseHelper dbHelper = new DatabaseHelper("students.db");

            // 创建学校实例
            School school = new School
            {
                Name = "学校1"
            };

            // 创建班级实例
            Class @class = new Class
            {
                Name = "班级1"
            };

            // 创建学生实例
            Student student = new Student
            {
                Name = "学生1",
                Age = 18
            };

            // 创建学校、班级、学生
            dbHelper.CreateSchool(school);
            dbHelper.CreateClass(@class, school.Id);
            dbHelper.CreateStudent(student, @class.Id);

            // 查询所有学校
            List<School> schools = dbHelper.GetAllSchools();
            foreach (var s in schools)
            {
                Console.WriteLine($"学校ID：{s.Id}，学校名称：{s.Name}");
                foreach (var c in s.Classes)
                {
                    Console.WriteLine($"班级ID：{c.Id}，班级名称：{c.Name}");
                    foreach (var stu in c.Students)
                    {
                        Console.WriteLine($"学生ID：{stu.Id}，学生姓名：{stu.Name}，学生年龄：{stu.Age}");
                    }
                }
            }

            Console.ReadLine();
        }
    }
}
